from django.db import connection
from django.http import JsonResponse
from rest_framework.views import APIView
from app01 import models
from django.db.models import Sum
from app01.models import CDZInfo
from django.db.models import Q, Count
from datetime import datetime, timezone
import dateutil.parser
from datetime import datetime
from django.utils import timezone
from app01.Baseview import MsgView, BasedAPIView
from app01.data.cdz_data import run_collections
import pymysql
import pytz
import json


class CDZInfo_Api(BasedAPIView):
    def get_page_all(self, request):
        try:
            page = int(request.GET.get("page", 1))  # 获取get请求传递的参数 page
            pagesize = int(request.GET.get("pagesize", 10))
        except Exception as e:
            page = 1
            pagesize = 10
        if page < 1:
            page = 1
        # dic = {}
        query = Q()
        orderNum = request.GET.get("orderNum", "")
        prepaidCard = request.GET.get("prepaidCard", "")
        cardType = request.GET.get("cardType", "")
        deviceName = request.GET.get("deviceName", "")
        idCardname = request.GET.get("idCardname", "")
        chargeStartTime = request.GET.getlist('chargeStartTime[]', None)

        # if prepaidCard:
        #     dic['prepaidCard__icontains'] = prepaidCard  # username是需要搜索的字段名 __icontains为使用模糊搜索
        # if orderNum:
        #     dic['orderNum__icontains'] = orderNum
        # if cardType:
        #     dic['cardType__icontains'] = cardType
        # if deviceName:
        #     dic['deviceName__icontains'] = deviceName

        # if len(chargeStartTime) == 2:
        #     try:
        #         start = chargeStartTime[0].replace("T", " ").replace(".000Z", "").replace("+", "")
        #         start_date = datetime.strptime(start, "%Y-%m-%d %H:%M:%S")
        #         # 解析日期
        #         end = dateutil.parser.isoparse(chargeStartTime[1])
        #         end_date = end.strftime("%Y-%m-%d %H:%M:%S")
        #         # print(start_date, end_date)
        #         # # 确保结束时间包含整天
        #         end_date = datetime.strptime(end_date, "%Y-%m-%d %H:%M:%S") + timezone.timedelta(days=1)
        #         # 应用过滤条件
        #         dic['chargeStartTime__range'] = (start_date, end_date)
        #
        #     except ValueError as e:
        #         print("error")
        if prepaidCard:
            query = query & Q(prepaidCard__icontains=prepaidCard)
        if orderNum:
            query = query & Q(orderNum__icontains=orderNum)
        if cardType:
            query = query & Q(cardType__icontains=cardType)
        if idCardname:
            query = query & Q(idCardname__icontains=idCardname)
            # for single_idCarName in idCardname:
            #     query |= Q(idCardname__icontains=single_idCarName.strip())
        if deviceName:
            for single_deviceName in deviceName:
                query |= Q(deviceName__icontains=single_deviceName.strip())
        if len(chargeStartTime) == 2:
            try:
                start = chargeStartTime[0].replace("T", " ").replace(".000Z", "").replace("+", "")
                start_date = datetime.strptime(start, "%Y-%m-%d %H:%M:%S")
                # 解析日期
                end = dateutil.parser.isoparse(chargeStartTime[1])
                end_date = datetime.strptime(end.strftime("%Y-%m-%d %H:%M:%S"),
                                             "%Y-%m-%d %H:%M:%S") + timezone.timedelta(days=1)
                query &= Q(chargeStartTime__range=(start_date, end_date))
            except ValueError as e:
                return JsonResponse(f"Error parsing date: {e}")

        # datas = CDZInfo.objects.filter(query).values()  # 从数据库中提取数据
        datas = CDZInfo.objects.filter(query).values()
        total_time_difference_seconds = 0
        data_list = []
        for item in datas:
            item['chargeStartTime'] = item['chargeStartTime'].strftime("%Y-%m-%d %H:%M:%S")
            item['chargeEndTime'] = item['chargeEndTime'].strftime("%Y-%m-%d %H:%M:%S")
            data_list.append(item)
            dt_tart = datetime.strptime(item['chargeStartTime'], "%Y-%m-%d %H:%M:%S")
            unix_tart = int(dt_tart.timestamp())
            dt_end = datetime.strptime(item['chargeEndTime'], "%Y-%m-%d %H:%M:%S")
            unix_end = int(dt_end.timestamp())
            time_difference_seconds = unix_end - unix_tart

            total_time_difference_seconds += time_difference_seconds  # 累计时间差
            ## 时分秒格式化
            hours = time_difference_seconds // 3600
            minutes = (time_difference_seconds % 3600) // 60
            seconds = time_difference_seconds % 60
            time_difference = f"{hours:02d}:{minutes:02d}:{seconds:02d}"
            item['timeDifferenceSeconds'] = time_difference
        hours = total_time_difference_seconds // 3600
        minutes = (total_time_difference_seconds % 3600) // 60
        seconds = total_time_difference_seconds % 60
        time_difference_total = f"{hours:02d}:{minutes:02d}:{seconds:02d}"

        start_page = page * pagesize - pagesize
        end_page = page * pagesize
        paginated_data = data_list[start_page:end_page]  ##分页后的数据list(paginated_data.values())

        ## type1 = models.CDZInfo.objects.values_list('cardType').annotate(Count('cardType'))  #获取分类数量
        total = models.CDZInfo.objects.filter(query).count()  # 查询数据条数
        sum_chargeAmount = models.CDZInfo.objects.filter(query).aggregate(Sum('chargeAmount'))
        sum_chargeElec = models.CDZInfo.objects.filter(query).aggregate(Sum('chargeElec'))
        sum_devName = models.CDZInfo.objects.filter(query).values('deviceName').annotate(count=Count('id'))

        data = {
            "total": total,
            "data": list(paginated_data),
            "sum_chargeAmount": sum_chargeAmount,
            "sum_chargeElec": sum_chargeElec,
            "sum_devName": list(sum_devName),
            "time_difference_total": time_difference_total
        }
        return super().success(data=data)

    def shell_script(self, reqeust):
        try:
            print("run_collections")
            run_collections()
            print("run_collections_finsh")
            try:
                with connection.cursor() as cursor:
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润慧通-百世杨总-备用卡2" WHERE `prepaidCard` = "0000000095DD2F7A";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润慧通-百世杨总-备用卡1" WHERE `prepaidCard` = "0000000020F40E2C";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润速达339卡" WHERE `prepaidCard` = "00000000E44A8897";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润速达-888卡" WHERE `prepaidCard` = "00000000E3F8EA47";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润速达-806卡" WHERE `prepaidCard` = "0000000020E8ACFC";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "园区-车辆租赁" WHERE `prepaidCard` = "00000000E3F8E2E7";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润慧通-百世杨总主卡" WHERE `prepaidCard` = "00000000873FBE0A";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润速达-239卡" WHERE `prepaidCard` = "00000000E54B8557";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润速达001卡" WHERE `prepaidCard` = "00000000E4A10EE7";')
                    cursor.execute(
                        'UPDATE `test_api`.`app01_cdzinfo` SET `idCardname` = "天润慧通-百世杨总-备用卡3" WHERE `prepaidCard` = "000000005145B947";')
                # rows = cursor.fetchall()
                    return super().success(msg="sql执行完成")
            except Exception as e:
                return super().error(msg=str(e))
        except Exception as e:
            return super().error(msg=str(e))
